
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 09/22/2012 11:54:45
-- Generated from EDMX file: D:\TFS\Projects\HDWebSite\UML\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [HDDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Employee'
CREATE TABLE [dbo].[Employee] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Gender] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL,
    [HappinessIndex] nvarchar(max)  NOT NULL,
    [Picture] nvarchar(max)  NOT NULL,
    [BirthDay] datetime  NULL,
    [Password] nvarchar(max)  NOT NULL,
    [LoginName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'News'
CREATE TABLE [dbo].[News] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Level] nvarchar(max)  NOT NULL,
    [PublishDate] datetime  NOT NULL,
    [EmployeeId] int  NOT NULL,
    [NewsTypeId] int  NOT NULL,
    [TimeStamp] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Posts'
CREATE TABLE [dbo].[Posts] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [CreateDate] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [EmployeeId] int  NOT NULL,
    [PostsTypeId] int  NOT NULL
);
GO

-- Creating table 'TaskInitiation'
CREATE TABLE [dbo].[TaskInitiation] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [WeekDate] nvarchar(max)  NOT NULL,
    [TaskValue] nvarchar(max)  NOT NULL,
    [EmployeeId] int  NOT NULL,
    [TaskNameId] int  NOT NULL,
    [TaskId] int  NOT NULL,
    [Employee_Id] int  NOT NULL
);
GO

-- Creating table 'TaskCompleted'
CREATE TABLE [dbo].[TaskCompleted] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [CompletedValue] nvarchar(max)  NOT NULL,
    [Date] nvarchar(max)  NOT NULL,
    [TaskId] int  NOT NULL
);
GO

-- Creating table 'Department'
CREATE TABLE [dbo].[Department] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Task'
CREATE TABLE [dbo].[Task] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Decription] nvarchar(max)  NOT NULL,
    [DefaultValue] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PostsType'
CREATE TABLE [dbo].[PostsType] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'NewsType'
CREATE TABLE [dbo].[NewsType] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Role'
CREATE TABLE [dbo].[Role] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Decription] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'DepartmentEmployee'
CREATE TABLE [dbo].[DepartmentEmployee] (
    [Departments_Id] int  NOT NULL,
    [Employees_Id] int  NOT NULL
);
GO

-- Creating table 'RoleEmployee'
CREATE TABLE [dbo].[RoleEmployee] (
    [Roles_Id] int  NOT NULL,
    [Employees_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Employee'
ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT [PK_Employee]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'News'
ALTER TABLE [dbo].[News]
ADD CONSTRAINT [PK_News]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Posts'
ALTER TABLE [dbo].[Posts]
ADD CONSTRAINT [PK_Posts]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TaskInitiation'
ALTER TABLE [dbo].[TaskInitiation]
ADD CONSTRAINT [PK_TaskInitiation]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TaskCompleted'
ALTER TABLE [dbo].[TaskCompleted]
ADD CONSTRAINT [PK_TaskCompleted]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Department'
ALTER TABLE [dbo].[Department]
ADD CONSTRAINT [PK_Department]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Task'
ALTER TABLE [dbo].[Task]
ADD CONSTRAINT [PK_Task]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'PostsType'
ALTER TABLE [dbo].[PostsType]
ADD CONSTRAINT [PK_PostsType]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'NewsType'
ALTER TABLE [dbo].[NewsType]
ADD CONSTRAINT [PK_NewsType]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Role'
ALTER TABLE [dbo].[Role]
ADD CONSTRAINT [PK_Role]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Departments_Id], [Employees_Id] in table 'DepartmentEmployee'
ALTER TABLE [dbo].[DepartmentEmployee]
ADD CONSTRAINT [PK_DepartmentEmployee]
    PRIMARY KEY NONCLUSTERED ([Departments_Id], [Employees_Id] ASC);
GO

-- Creating primary key on [Roles_Id], [Employees_Id] in table 'RoleEmployee'
ALTER TABLE [dbo].[RoleEmployee]
ADD CONSTRAINT [PK_RoleEmployee]
    PRIMARY KEY NONCLUSTERED ([Roles_Id], [Employees_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [EmployeeId] in table 'News'
ALTER TABLE [dbo].[News]
ADD CONSTRAINT [FK_EmployeeNews]
    FOREIGN KEY ([EmployeeId])
    REFERENCES [dbo].[Employee]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeNews'
CREATE INDEX [IX_FK_EmployeeNews]
ON [dbo].[News]
    ([EmployeeId]);
GO

-- Creating foreign key on [EmployeeId] in table 'Posts'
ALTER TABLE [dbo].[Posts]
ADD CONSTRAINT [FK_EmployeeForum]
    FOREIGN KEY ([EmployeeId])
    REFERENCES [dbo].[Employee]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeForum'
CREATE INDEX [IX_FK_EmployeeForum]
ON [dbo].[Posts]
    ([EmployeeId]);
GO

-- Creating foreign key on [Departments_Id] in table 'DepartmentEmployee'
ALTER TABLE [dbo].[DepartmentEmployee]
ADD CONSTRAINT [FK_DepartmentEmployee_Department]
    FOREIGN KEY ([Departments_Id])
    REFERENCES [dbo].[Department]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Employees_Id] in table 'DepartmentEmployee'
ALTER TABLE [dbo].[DepartmentEmployee]
ADD CONSTRAINT [FK_DepartmentEmployee_Employee]
    FOREIGN KEY ([Employees_Id])
    REFERENCES [dbo].[Employee]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartmentEmployee_Employee'
CREATE INDEX [IX_FK_DepartmentEmployee_Employee]
ON [dbo].[DepartmentEmployee]
    ([Employees_Id]);
GO

-- Creating foreign key on [Employee_Id] in table 'TaskInitiation'
ALTER TABLE [dbo].[TaskInitiation]
ADD CONSTRAINT [FK_TaskEmployee]
    FOREIGN KEY ([Employee_Id])
    REFERENCES [dbo].[Employee]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskEmployee'
CREATE INDEX [IX_FK_TaskEmployee]
ON [dbo].[TaskInitiation]
    ([Employee_Id]);
GO

-- Creating foreign key on [TaskId] in table 'TaskCompleted'
ALTER TABLE [dbo].[TaskCompleted]
ADD CONSTRAINT [FK_TaskTaskCompleted]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[Task]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTaskCompleted'
CREATE INDEX [IX_FK_TaskTaskCompleted]
ON [dbo].[TaskCompleted]
    ([TaskId]);
GO

-- Creating foreign key on [PostsTypeId] in table 'Posts'
ALTER TABLE [dbo].[Posts]
ADD CONSTRAINT [FK_PostsTypePosts]
    FOREIGN KEY ([PostsTypeId])
    REFERENCES [dbo].[PostsType]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PostsTypePosts'
CREATE INDEX [IX_FK_PostsTypePosts]
ON [dbo].[Posts]
    ([PostsTypeId]);
GO

-- Creating foreign key on [NewsTypeId] in table 'News'
ALTER TABLE [dbo].[News]
ADD CONSTRAINT [FK_NewsTypeNews]
    FOREIGN KEY ([NewsTypeId])
    REFERENCES [dbo].[NewsType]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NewsTypeNews'
CREATE INDEX [IX_FK_NewsTypeNews]
ON [dbo].[News]
    ([NewsTypeId]);
GO

-- Creating foreign key on [TaskId] in table 'TaskInitiation'
ALTER TABLE [dbo].[TaskInitiation]
ADD CONSTRAINT [FK_TaskTaskInitiation]
    FOREIGN KEY ([TaskId])
    REFERENCES [dbo].[Task]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TaskTaskInitiation'
CREATE INDEX [IX_FK_TaskTaskInitiation]
ON [dbo].[TaskInitiation]
    ([TaskId]);
GO

-- Creating foreign key on [Roles_Id] in table 'RoleEmployee'
ALTER TABLE [dbo].[RoleEmployee]
ADD CONSTRAINT [FK_RoleEmployee_Role]
    FOREIGN KEY ([Roles_Id])
    REFERENCES [dbo].[Role]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Employees_Id] in table 'RoleEmployee'
ALTER TABLE [dbo].[RoleEmployee]
ADD CONSTRAINT [FK_RoleEmployee_Employee]
    FOREIGN KEY ([Employees_Id])
    REFERENCES [dbo].[Employee]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleEmployee_Employee'
CREATE INDEX [IX_FK_RoleEmployee_Employee]
ON [dbo].[RoleEmployee]
    ([Employees_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------